#coding=utf8
import datetime
import re
import json
import sqlite3
from copy import deepcopy
from typing import AnyStr, List
import time
import pymysql
import requests
from pymysql import Connection
from dataForTest.dataMethod import getPorpsOfMysql,getSqliteIndex
from utils.environments_json import urlHead, json_data
from utils.path_generator import filePath


def count():
    i = 0
    while (True):
        yield i
        i += 1

class reqDB:
    def __init__(self, dbCon: sqlite3.Connection,tableName:AnyStr,dbConMysql:Connection):
        self.con = dbCon
        self.dbConMysql=dbConMysql
        self.cur =self.con.cursor()
        self.dbCur=self.dbConMysql.cursor()
        self.tableName=tableName
        self.exeOrderIndex=getSqliteIndex(self.con,tableName,"exeOrder")
        self.apiIndex=getSqliteIndex(self.con,tableName,"api")
        self.reqTypeIndex=getSqliteIndex(self.con,tableName,"reqType")
        self.paramsIndex=getSqliteIndex(self.con,tableName,"params")
        self.idExpression=getSqliteIndex(self.con,tableName,"idExpression")
        self.reU=reUtils()
        self.tableIdsDic={}

    @staticmethod
    def writeJsonFile(jsonFilename,Obj):
        with open(filePath.sqlite_path + jsonFilename,"w",encoding="utf-8") as f:
            json.dump(Obj,f)

    @staticmethod
    def readJsonFile(jsonFilename):
        with open(filePath.sqlite_path + jsonFilename,"r",encoding="utf-8") as f:
            return json.load(f)


    def reqByMethod(self,dataOnlyOne:list):
        headers={"mode":"debug"}
        if dataOnlyOne[self.reqTypeIndex]=="POST":
            headers['Content-Type'] ="application/json;charset=utf-8"
            response = requests.post(url=urlHead+dataOnlyOne[self.apiIndex], data=dataOnlyOne[self.paramsIndex].encode('utf-8'),headers=headers)
        elif dataOnlyOne[self.reqTypeIndex]=="GET":
            response = requests.post(url=urlHead+dataOnlyOne[self.apiIndex], params=json.loads(dataOnlyOne[self.paramsIndex],encoding="utf_8"))
        if response.json()["code"]!=0 or response.status_code!=200:
            print(urlHead+dataOnlyOne[self.apiIndex]+":数据创建失败！")
            print("请求参数："+dataOnlyOne[self.paramsIndex])
            print("接口响应:"+response.text)


    def exeReq(self,dataAfterReplace:list):
        self.reqByMethod(dataAfterReplace)


    def replaceDataAndReq(self):
        i = 0
        while (True):
            sql = "select *  from  %s limit %s,%s" % (self.tableName, 0 + i, 1 + i)
            self.cur.execute(sql)
            res = self.cur.fetchone()
            if res is None:
                break
            res=list(res)
            reItor=self.reU.getConditionAndResItor(res[self.paramsIndex])
            while(True):
                try:
                    strCON,strRES=next(reItor)
                    selectR=self.selectRes(strCON,strRES)
                    self.dbCur.execute(selectR)
                    fetchRes=self.dbCur.fetchone()
                    if fetchRes is not None:
                        fetchRes=fetchRes[0]
                    res[self.paramsIndex]=res[self.paramsIndex].replace(strCON+"->"+strRES,str(fetchRes),1)
                except StopIteration:
                    break
            self.exeReq(res)
            i += 1
            if res[self.idExpression] is not None:
                self.idExpressHandle(res[self.idExpression])




    def selectRes(self,strCON:AnyStr,strRES:AnyStr):
        strRES_tmp= self.varifyCondition([strRES,])
        if strRES_tmp==[]:
            return None
        tmp=self.reU.pattern3.findall(strCON)
        str_tmp,tableAsDic=self.varifyLsitCondition(tmp)
        if str_tmp=="" or tableAsDic=={}:
            return str_tmp
        selectStr=strRES_tmp[0].split(".",1)[0]
        selectStr=strRES_tmp[0].replace(selectStr,tableAsDic[selectStr],1)
        str_tmp="select distinct("+selectStr+") from " +str_tmp+";"
        if "where" not in str_tmp:
            return None
        return str_tmp


    def varifyLsitCondition(self,L:List[AnyStr]):
        tmpLWhere=[]
        tmpLJoin=[]
        str_tmp=""
        charCur=count()
        for i in L:
            table_prop1,table_prop2=i.split("=",1)
            try:
                table1,prop1=table_prop1.split(".",1)
            except Exception:
                continue
            try :
                table2,prop2 =table_prop2.split(".",1)
            except Exception:
                tmpLWhere.append(i)
                continue

            try:
                table1Props=getPorpsOfMysql(self.dbConMysql,table1)
                if prop1  not in table1Props:
                    continue
            except Exception:
                continue
            try:
                table2Props = getPorpsOfMysql(self.dbConMysql, table2)
                if prop2 not in table2Props:
                    tmpLWhere.append(i)
                    continue
            except Exception:
                tmpLWhere.append(i)
                continue
            tmpLJoin.append(i)
        if len(tmpLWhere)==0:
            return None
        tableUsed=[]
        tableAsDic={}
        if len(tmpLJoin)!=0:
            tmpSearch=self.reU.pattern5.search(tmpLJoin[0])
            table1,prop1 = tmpSearch.group(1).split(".",1)
            table2,prop2 = tmpSearch.group(2).split(".",1)
            no1=str(next(charCur))
            no2=str(next(charCur))
            str_tmp += table1 + " T"+no1+" join " + table2 + " T"+no2+ " on "
            tmpAs="T"+no1+"."+prop1+"="+"T"+no2+"."+prop2
            str_tmp+=tmpAs
            tableAsDic[table1]="T"+no1
            tableAsDic[table2] = "T" + no2
            tableUsed.append(table1)
            tableUsed.append(table2)
            tmpLJoin.remove(tmpLJoin[0])

            tmpLJoinCopy = deepcopy(tmpLJoin)
            while(tmpLJoinCopy!=[] and tmpLJoinCopy is not None):
                length = len(tmpLJoinCopy)
                for i in tmpLJoin:
                    searRES=self.reU.pattern5.search(i)
                    table1, prop1 = searRES.group(1).split(".", 1)
                    table2, prop2 = searRES.group(2).split(".", 1)
                    if table1 in tableUsed:
                        noTmp=str(next(charCur))
                        str_tmp+=" join "+table2+ " T"+noTmp+" on "
                        tmpAs=tableAsDic[table1]+"."+prop1+"="+"T"+noTmp+"."+prop2
                        str_tmp+=tmpAs
                        tableAsDic[table2]="T"+noTmp
                        tmpLJoinCopy.remove(i)

                    elif table2 in tableUsed:
                        noTmp = str(next(charCur))
                        str_tmp += " join " + table1 + " T" + noTmp + " on "
                        tmpAs ="T" + noTmp + "." + prop1+ "=" +tableAsDic[table2] + "." + prop2
                        str_tmp += tmpAs
                        tableAsDic[table1] = "T" + noTmp
                        tmpLJoinCopy.remove(i)
                if length==len(tmpLJoinCopy) or len(tmpLJoinCopy)==0:
                    break
                tmpLJoin=deepcopy(tmpLJoinCopy)
        else:
            no1=str(next(charCur))
            tmpSearch = self.reU.pattern4.search(tmpLWhere[0])
            table = tmpSearch.group(1).split(".",1)[0]
            str_tmp += table + " T" + no1
            tableAsDic[table]="T" + no1

        str_tmp += " where "
        tmpSearch=self.reU.pattern4.search(tmpLWhere[0])
        table = tmpSearch.group(1).split(".",1)[0]
        whereStr=tmpLWhere[0].replace(table,tableAsDic[table],1)
        str_tmp+=whereStr+" "
        if len(tmpLWhere)>1:
            for j in range(1,len(tmpLWhere)):
                tmpSearch == self.reU.pattern4.search(tmpLWhere[j])
                table = tmpSearch.group(1).split(".",1)[0]
                whereStr = tmpLWhere[j].replace(table, tableAsDic[table], 1)
                str_tmp += " and " +whereStr + " "

        return str_tmp,tableAsDic


    def varifyCondition(self,L:List[AnyStr]):
        tmpL=deepcopy(L)
        for i in L:
            table1,prop1=i.split(".",1)
            table1Props=getPorpsOfMysql(self.dbConMysql,table1)
            if prop1 not in table1Props:
                tmpL.remove(i)
        return tmpL

    def idExpressHandle(self,idStrExp:AnyStr):
        reItor = self.reU.getIdsConditionAndResResItor(idStrExp)
        while (True):
            try:
                strCON_RES=next(reItor)
                strCON, strRES =strCON_RES
                selectR = self.selectRes(strCON, strRES)
                self.dbCur.execute(selectR)
                fetchRes=self.dbCur.fetchall()
                if fetchRes is not None and len(fetchRes)!=0:
                    if strRES not in self.tableIdsDic.keys() :
                        L=[]
                        for i in fetchRes:
                            L.append(i[0])
                        self.tableIdsDic[strRES]=L
                        self.tableIdsDic[strRES].sort()
                        reqDB.writeJsonFile("WJF.json",self.tableIdsDic)
                    else:
                        L=self.tableIdsDic[strRES]
                        L=set(L)
                        for i in fetchRes:
                            L.add(i[0])
                        self.tableIdsDic[strRES]=list(L)
                        self.tableIdsDic[strRES].sort()
                        reqDB.writeJsonFile("WJF.json", self.tableIdsDic)

            except StopIteration:
                break



    def deleData(self):
        if len(self.tableIdsDic)>0:
            for i in self.tableIdsDic.keys():
                table,prop=i.split(".",1)
                L=self.tableIdsDic[i]
                if len(L)==1:
                    sql = "delete from %s where %s = %s" % (table, prop,L[0])
                else:
                    sql="delete from %s where %s in %s"%(table,prop,tuple(L))
                self.dbCur.execute(sql)
                self.dbConMysql.commit()


    def deleDataByHand(self,jsonFileName):
        tableIdsDic=reqDB.readJsonFile(jsonFileName)
        if len(tableIdsDic)>0:
            for i in tableIdsDic.keys():
                table,prop=i.split(".",1)
                L=tableIdsDic[i]
                if len(L)==1:
                    sql = "delete from %s where %s = %s" % (table, prop,L[0])
                else:
                    sql="delete from %s where %s in %s"%(table,prop,tuple(L))
                self.dbCur.execute(sql)
                self.dbConMysql.commit()

class reUtils:
    def __init__(self):
        self.pattern1 = re.compile(r'"\w+":"?((\w+\.\w+=.+?)->(\w+\.\w+))"?')
        self.pattern2=re.compile(r'"?(\w+\.\w+=.+?)->(\w+\.\w+)"?')
        # self.pattern3=re.compile(r"(?<!,)\b\w+\.\w+=[^,]+?(?=,)|(?<=,)\b\w+\.\w+=[^,]+?(?=,)|(?<=,)\b\w+\.\w+=[^,]+(?!,)|\b\w+\.\w+=[^,=]+")
        self.pattern3=re.compile(r'\b\w+\.\w+=.+?(?=,\w+\.\w+=)|\b\w+\.\w+=.+')
        self.pattern4 = re.compile(r"(\b\w+\.\w+)=")
        self.pattern5 = re.compile(r"(\b\w+\.\w+)=(\w+\.\w+\b)")
    def getConditionAndResItor(self,reqJson:AnyStr):
        matchRes = self.pattern1.finditer(reqJson)
        while (True):
            try:
                macthOb = next(matchRes)
                strCondition=macthOb.group(2)
                strRes=macthOb.group(3)
                yield strCondition, strRes
            except StopIteration:
                break

    def getIdsConditionAndResResItor(self,idStrExp:AnyStr):
        matchRes = self.pattern2.finditer(idStrExp)
        while (True):
            try:
                macthOb = next(matchRes)
                strCondition = macthOb.group(1)
                strRes = macthOb.group(2)
                yield strCondition, strRes
            except StopIteration:
                break





if __name__=="__main__":
    # mysql_json = json_data.get(json_data.get("current_env")).get("mysql")
    # server = mysql_json.get("server")
    # port = int(mysql_json.get("port"))
    # user = mysql_json.get("username")
    # passwd = mysql_json.get("password")
    # database = mysql_json.get("database")
    # db = pymysql.connect(host=server, port=port, user=user, password=passwd, db=database, charset='utf8',
    #                      autocommit=True)
    # mycur=db.cursor()
    struct_time = datetime.datetime.now()
    zone = datetime.timedelta(days=1)
    yesterdayStr = datetime.datetime.strftime(struct_time - zone, "%Y-%m-%d")
    yesterdayStr1 = datetime.datetime.strftime(struct_time - zone, "%Y-%m-%d %H:%M:%S")
    yesterdayStr2 = datetime.datetime.strftime(struct_time, "%Y-%m-%d")
    print(yesterdayStr)
    print(yesterdayStr1)
    print(yesterdayStr2)



